1 Setup

First, we need to set up our environment. The code we need for loading packages can be expanded below.

Load Packages

Packages:

# load primary packages
library(here) # for relative directories
library(showtext) # fonts
library(readxl) # read data from Excel
library(labelled) # column labelling
library(janitor) # 2-way tables; exploratory analysis
library(scales) # formatting percentages
library(patchwork) # combining plots
library(ggeasy) # using column labels in plots
library(gt) # nice HTML tables
library(tinytable) # nice HTML/PDF tables without dependencies
library(gtExtras) # rich HTML tables (added on to functionality of {gt}
library(dlookr) # creating data dictionaries
library(psych) # exploratory analysis; summary stats
library(tidyverse) # multifunctional collection of packages

# knitr options
# knitr::opts_chunk$set(out.width = 700, out.height = 500)

2 Introduction

I am a big fan of the Thomas Lady Terriers basketball team, and I am forever grateful to Wright Media and Newstalk KCLI for broadcasting Terrier and Lady Terrier sports on Terrier TV, allowing me to watch the games while living out of state.

I have recorded and maintained player and team statistics for each game of the current season, but now I want to be able to automate some of the aggregations and maybe create some customized plots and tables from these stats. Enter R.

2.1 Load Game Data

2.1.1 Raw Data

The raw data is in Excel and cannot be downloaded from this website. However, you can email me and I can send it to you.

Let’s load the data using the readxl package:

# file path
path <- here("_input_data/Lady-Terriers_STATS_updated-15JAN2024.xlsx")

# sheet names
sheets <- excel_sheets(path = path)

# choose all but first two sheets and reverse their order
games <- rev(sheets[-c(1, 2)]) |> 
  map_df(
    ~ read_excel(
      path = path, 
      sheet = .x, 
      range = "A4:V25",
      col_types = "text"),
    .id = "game")

2.1.2 Scores by Quarter

# setup
scores_by_qtr <- games |> 
  filter(is.na(FTA)) |> 
  drop_na("Name") |> 
  filter(Name != "SCORE BY QUARTER") |> 
  mutate(
    team = str_to_title(Name),
    game = as.numeric(game)) |> 
  select(
    game, team, 
    Q1_pts = "Fouls",
    Q2_pts = "2PA",
    Q3_pts = "2PM",
    Q4_pts = "PCT...6") |> 
  mutate(
    across(c(Q1_pts:Q4_pts), as.numeric))

# one row per quarter per team
scores_qtr_long <- scores_by_qtr |> 
  pivot_longer(
    cols = -c(1,2),
    names_to = "qtr",
    values_to = "points") |> 
  mutate(quarter = str_sub(qtr, start = 2, end = 2) |> as.numeric()) |> 
  select(1, 2, 5, 4)

# one row per game per team
scores_qtr_wide <- scores_by_qtr |> 
  select(
    game, team,
    q1 = Q1_pts,
    q2 = Q2_pts,
    q3 = Q3_pts,
    q4 = Q4_pts) |> 
  mutate(
    h1 = q1 + q2,
    h2 = q3 + q4,
    g = h1 + h2) |> 
  set_variable_labels(
    game = "Game Number",
    team = "Team Name",
    q1 = "1st Quarter Points",
    q2 = "2nd Quarter Points",
    q3 = "3rd Quarter Points",
    q4 = "4th Quarter Points",
    h1 = "1st Half Points",
    h2 = "2nd Half Points",
    g = "Total Points")

qtr_wide_col_labs <- scores_qtr_wide
names(qtr_wide_col_labs) <- as.character(var_label(qtr_wide_col_labs))
tinytable::tt(qtr_wide_col_labs, theme = "striped")

Table 1: Scores by Quarter, Half and Game

Game Number	Team Name	1st Quarter Points	2nd Quarter Points	3rd Quarter Points	4th Quarter Points	1st Half Points	2nd Half Points	Total Points
1	Thomas	7	13	21	11	20	32	52
1	Cordell	11	10	4	9	21	13	34
2	Thomas	23	17	18	15	40	33	73
2	Geary	8	12	10	19	20	29	49
3	Thomas	7	19	7	10	26	17	43
3	Arapaho	14	13	4	13	27	17	44
4	Thomas	9	14	10	6	23	16	39
4	Burns Flat-Dill City	0	4	3	4	4	7	11
5	Thomas	6	7	5	5	13	10	23
5	Hobart	6	12	8	7	18	15	33
6	Thomas	8	18	6	7	26	13	39
6	Mangum	10	10	14	13	20	27	47
7	Thomas	19	10	11	12	29	23	52
7	Calumet	2	5	1	16	7	17	24
8	Thomas	5	11	6	11	16	17	33
8	Canton	15	17	4	7	32	11	43
9	Thomas	10	13	10	8	23	18	41
9	Union City	8	9	8	8	17	16	33
10	Thomas	14	12	5	14	26	19	45
10	Ringwood	5	4	4	8	9	12	21
11	Thomas	8	6	11	7	14	18	32
11	Canton	16	9	4	9	25	13	38
12	Thomas	3	13	15	9	16	24	40
12	Okeene	11	6	6	15	17	21	38
13	Thomas	4	11	9	8	15	17	32
13	Lookeba-Sickles	22	15	19	19	37	38	75
14	Thomas	10	7	8	8	17	16	33
14	Okarche	8	15	7	17	23	24	47
15	Thomas	7	11	8	13	18	21	39
15	Gracemont	4	3	3	16	7	19	26
16	Thomas	11	6	7	9	17	16	33
16	Corn Bible	3	7	6	10	10	16	26

2.1.3 Opponent Game Stats

opponent_stats <- games |> 
  drop_na(Name) |> 
  filter(Name != "THOMAS") |> 
  slice_tail(n = 3, by = game) |> 
  slice_head(n = 1, by = game) |> 
  select(-c(2, 7, 10, 13, 16, 21, 22, 23)) |> 
  mutate(
    game = as.numeric(game),
    # opponent = if_else(Name == "BFDC", "Burns Flat-Dill City", Name),
    opponent = str_to_title(Name),
    across(3:15, as.numeric), 
    .after = Name) |> 
  mutate(PF = Fouls, .after = TO) |> 
  select(-c(Name, Fouls)) |> 
  relocate(opponent, .after = game)

2.1.4 Player Game Stats

player_stats <- games |> 
  drop_na(`#`) |>
  rename(
    number = `#`,
    name = Name,
    PF = Fouls) |> 
  mutate(
    number = as.numeric(number),
    game = as.numeric(game),
    across(4:23, as.numeric)) |> 
  select(-c(7, 10, 13, 16))

2.2 Load Game Information

This includes game types (home, road, or neutral), locations, opponents and dates:

dates_prep <- rev(sheets[-c(1, 2)]) |> 
  map_df(
    ~ read_excel(
      path = path, 
      sheet = .x, 
      range = "A1:U1",
      col_names = FALSE,
      col_types = c(
        "date", "text", "text", "text", "text", "text", "text", "text",
        "text", "text", "text", "text", "text", "text", "text", "text",
        "text", "text", "text", "text", "text")),
    .id = "game")
dates <- dates_prep |> 
  mutate(
    game = as.numeric(game),
    date = as.Date(`...1`),
    location = str_sub(`...9`, start = 3L, end = -1L),
    opponent = `...21`,
    .keep = "none") |> 
  mutate(
    type = case_when(
      location == "Thomas" ~ "Home",
      str_detect(location, "\\*") ~ "Tournament",
      str_detect(location, "\\†") ~ "Tournament",
      str_detect(location, "\\‡") ~ "Tournament",
      str_detect(location, "\\⁰") ~ "Playoff",
      .default = "Away") |> 
      factor(levels = c("Home", "Away", "Tournament", "Playoff")) |> 
      fct_drop(),
    .after = date) |> 
  mutate(
    location = if_else(
      type == "Tournament",
      str_sub(location, start = 1L, end = -2L),
      location))

3 Summarize Data

3.1 Opponent Game Stats

opp_scores_qtr_wide <- scores_qtr_wide |> 
  filter(team != "Thomas") |> 
  select(1, 3:6)

opp_summary <- dates |> 
  select(game, date, location, type) |> 
  left_join(opponent_stats, by = "game") |> 
  left_join(opp_scores_qtr_wide, by = "game") |> 
  mutate(
    team = opponent,
    PTS = FTM + (2 * `2PM`) + (3 * `3PM`),
    `2PT_PCT` = round(`2PM` / `2PA`, digits = 3),
    `3PT_PCT` = round(`3PM` / `3PA`, digits = 3),
    FT_PCT = round(FTM / FTA, digits = 3),
    REB = OREB + DREB) |> 
  select(-opponent) |> 
  select(
    game, date, location, type, team, 
    PTS, OREB, DREB, REB, AST, STL, BLK, TO, PF,
    `2PA`, `2PM`, `2PT_PCT`, `3PA`, `3PM`, `3PT_PCT`, FTA, FTM, FT_PCT,
    q1, q2, q3, q4)

3.2 Thomas Game Stats

tt_scores_qtr_wide <- scores_qtr_wide |> 
  filter(team == "Thomas") |> 
  select(1, 3:6)

tt_dates <- dates |> 
  select(game, date, location, type)

tt_summary <- player_stats |> 
  select(-c(2:3, 17:19)) |> 
  summarise(
    across(PF:TO, sum),
    .by = game) |> 
  left_join(tt_scores_qtr_wide, by = "game") |> 
  right_join(tt_dates, by = "game") |> 
  mutate(
    team = "Thomas",
    PTS = FTM + (2 * `2PM`) + (3 * `3PM`),
    `2PT_PCT` = round(`2PM` / `2PA`, digits = 3),
    `3PT_PCT` = round(`3PM` / `3PA`, digits = 3),
    FT_PCT = round(FTM / FTA, digits = 3),
    REB = OREB + DREB) |> 
  select(
    game, date, location, type, team, 
    PTS, OREB, DREB, REB, AST, STL, BLK, TO, PF,
    `2PA`, `2PM`, `2PT_PCT`, `3PA`, `3PM`, `3PT_PCT`, FTA, FTM, FT_PCT,
    q1, q2, q3, q4)

3.3 Combined Game Stats

game_summaries <- bind_rows(tt_summary, opp_summary)

game_summary_tt <- game_summaries |> 
  arrange(game) |> 
  select(game, date, type, team, PTS:TO) 

tinytable::tt(game_summary_tt, theme = "striped")

Table 2: Game Stats by Team

game	date	type	team	PTS	OREB	DREB	REB	AST	STL	BLK	TO
1	2023-11-28	Away	Thomas	52	8	29	37	14	1	6	19
1	2023-11-28	Away	Cordell	34	5	16	21	9	0	2	9
2	2023-12-01	Away	Thomas	73	13	24	37	12	12	6	14
2	2023-12-01	Away	Geary	49	8	14	22	7	6	3	19
3	2023-12-05	Home	Thomas	43	8	20	28	12	11	5	17
3	2023-12-05	Home	Arapaho	44	13	13	26	8	10	3	14
4	2023-12-07	Tournament	Thomas	39	7	21	28	7	12	2	16
4	2023-12-07	Tournament	Burns Flat-Dill City	11	3	11	14	3	6	3	24
5	2023-12-08	Tournament	Thomas	23	7	13	20	1	5	4	13
5	2023-12-08	Tournament	Hobart	33	5	18	23	6	8	4	18
6	2023-12-09	Tournament	Thomas	39	8	18	26	5	7	3	21
6	2023-12-09	Tournament	Mangum	47	9	14	23	7	7	0	18
7	2023-12-12	Away	Thomas	52	18	21	39	10	13	4	7
7	2023-12-12	Away	Calumet	24	6	12	18	5	5	5	18
8	2023-12-14	Home	Thomas	33	3	17	20	4	7	1	15
8	2023-12-14	Home	Canton	43	6	15	21	7	7	0	8
9	2023-12-18	Home	Thomas	41	3	17	20	6	10	0	17
9	2023-12-18	Home	Union City	31	6	10	16	7	9	1	20
10	2024-01-04	Tournament	Thomas	45	15	19	34	7	12	4	11
10	2024-01-04	Tournament	Ringwood	21	3	15	18	4	4	0	24
11	2024-01-05	Tournament	Thomas	32	6	12	18	4	4	0	9
11	2024-01-05	Tournament	Canton	38	7	12	19	5	4	1	12
12	2024-01-06	Tournament	Thomas	40	11	16	27	7	9	0	12
12	2024-01-06	Tournament	Okeene	38	7	9	16	6	3	2	13
13	2024-01-09	Away	Thomas	32	7	14	21	4	3	0	18
13	2024-01-09	Away	Lookeba-Sickles	75	11	18	29	20	8	2	10
14	2024-01-12	Away	Thomas	33	5	13	18	6	4	3	15
14	2024-01-12	Away	Okarche	47	9	12	21	8	6	2	11
15	2024-01-12	Away	Thomas	39	15	17	32	10	19	3	13
15	2024-01-12	Away	Gracemont	26	4	25	29	5	6	1	27
16	2024-01-15	Away	Thomas	33	8	13	21	7	10	8	14
16	2024-01-15	Away	Corn Bible	26	8	12	20	7	7	1	18

3.4 Player Stats

# one row per player per game
player_stats_detailed <- dates |> 
  select(-location) |> 
  left_join(player_stats, by = "game") |> 
  select(-PTS) |> 
  mutate(
    PTS = FTM + (2 * `2PM`) + (3 * `3PM`),
    `2PT_PCT` = round(`2PM` / `2PA`, digits = 3),
    `3PT_PCT` = round(`3PM` / `3PA`, digits = 3),
    FT_PCT = round(FTM / FTA, digits = 3),
    REB = OREB + DREB) |> 
  rename(`+/-` = `+ / -`) |> 
  select(
    game, date, type, opponent, num = number, name, 
    PTS, OREB, DREB, REB, AST, STL, BLK, TO, PF, MIN, `+/-`,
    `2PA`, `2PM`, `2PT_PCT`, `3PA`, `3PM`, `3PT_PCT`, FTA, FTM, FT_PCT)

# one row per player - SUMS
player_stats_sums <- player_stats_detailed |> 
  summarise(
    num = first(num),
    G = n(),
    across(
      c(PTS:`2PM`, `3PA`, `3PM`, FTA, FTM),
      sum),
    .by = name) |> 
  select(num, everything())

# one row per player - MEANS
player_stats_means <- player_stats_sums |> 
  mutate(
    across(
      c(4:20),
      ~ round(.x / G, digits = 1))) |> 
  mutate(
    `2PT_PCT` = round(`2PM` / `2PA`, digits = 3),
    `3PT_PCT` = round(`3PM` / `3PA`, digits = 3),
    FT_PCT = round(FTM / FTA, digits = 3),
    TS_PCT = round(PTS / (2 * (`2PA` + `3PA` + (0.44 * FTA))), digits = 3),
    `AST/TO` = round(AST / TO, digits = 2)
  )

player_stats_tt <- player_stats_means |> 
  arrange(desc(MIN)) |> 
  select(1:4, 7:14) 

tinytable::tt(player_stats_tt, theme = "striped")

Table 3: Player Stats (averages per game)

num	name	G	PTS	REB	AST	STL	BLK	TO	PF	MIN	+/-
5	Jorgen Stewart	16	11.1	3.4	1.5	1.7	0.3	1.9	1.8	29.0	4.6
15	Channing Wright	16	9.6	3.1	1.5	2.8	0.1	3.1	2.4	27.6	4.2
24	Madi Mannering	10	8.9	8.5	1.0	1.2	2.8	2.2	2.2	27.3	4.3
23	Kate Mannering	13	4.2	3.5	1.3	0.5	0.4	1.7	1.6	19.8	1.9
11	Annmarie Cometsevah	16	3.2	3.3	0.8	1.4	0.1	2.1	1.9	18.8	1.6
20	Abbi Crowdis	9	2.6	3.1	1.8	0.3	0.3	2.7	1.1	17.8	7.4
21	Jozlun Fite	16	3.4	3.8	0.3	0.5	0.2	1.5	1.2	16.6	1.6
14	McCartney Lisle	16	1.5	1.1	0.3	0.6	0.0	1.4	1.1	13.9	-0.3
22	Jaelyn Koontz	15	1.0	1.5	0.0	0.2	0.0	0.1	1.0	8.8	0.1
12	McKenzie McGuire	6	0.8	0.8	0.3	0.2	0.0	0.3	0.0	5.7	0.2
42	Sadie Kliewer	4	0.0	0.2	0.0	0.0	0.0	0.0	0.0	3.2	-2.8
35	Chesney Hazel	3	0.7	1.0	0.0	0.0	0.3	0.0	0.0	3.0	-1.7

3.5 Advanced Stats

3.5.1 Definitions

Let’s calculate some more advanced player stats, but first we will need to calculate a few game-level team stats. Namely:

• FGA - total field goals attempted (2PA + 3PA) by Thomas (TLT_FGA) or opponent (OPP_FGA)
• FGM - total field goals made (2PM + 3PM) by Thomas (TLT_FGM) or opponent (OPP_FGM)
• FTA - total free throws attempted by Thomas (TLT_FTA) or opponent (OPP_FTA)
• 3PA - total 3-pointers attempted by Thomas (TLT_3PA) or opponent (OPP_3PA)
• OREB - total offensive rebounds by Thomas (TLT_OREB) or their opponent (OPP_OREB)
• REB - total rebounds for Thomas (TLT_REB) or their opponent (OPP_REB)
• TO - total turnovers for Thomas (TLT_TO) or their opponent (OPP_TO)

The TLT and OPP prefixes are acronyms for “Thomas Lady Terriers” and “opponent”, respectively. In the formulas below, the TLT prefix is implied for any stats/metrics that don’t have a prefix displayed.

True Shooting Percentage (TS_PCT) - this metric incorporates shooting efficiency for free throws, 2-point shots and 3-point shots:

TS\_PCT=\frac{PTS}{2\big(FGA+(0.44 \times FTA)\big)} Estimated Possessions (POSS) - this estimates the number of posessions a team has in a game based on shots taken, offensive rebounds and turnovers:

POSS=0.5 \times \big(FGA+(0.475 \times FTA)-OREB+TO\big)

It should be noted that POSS is a team metric only. This metric, while useful as a measure of pace, is also used to calculate some of the player metrics described below.

Rebounding Percentage (REB_PCT) - measures a player’s total rebounding contribution to the game, and is adjusted for time spent on the court:

REB\_PCT=\frac{REB \times \frac{32}{MIN}}{TLT\_REB+OPP\_REB} If a player plays all 32 minutes of the game, this metric will provide the proportion of a player’s rebounds to the total game rebounds of both teams. For the average rebounder, this metric should be close to 0.1 since there is a 1 of 10 players on the court will get a given rebound.

Assist Ratio (AST_RT) - measures the percentage of teammate baskets a player assisted on, and is adjusted for time spent on the court:

AST\_RT=\frac{AST}{\bigg(\frac{MIN}{32}\times TLT\_FGM\bigg)-FGM}

Assist Percentage (AST_PCT) - measures the proportion of player assists per team possession, and is adjusted for time spent on the court:

AST\_PCT=\frac{AST}{\frac{MIN}{32}\times TLT\_POSS}

If a team plays at a fast pace, this metric adjusts for pace because it’s based on total team possessions. This metric penalizes a player when teammates miss shots or turn the ball over.

Steal Percentage (STL_PCT) - proportion of opponent’s possessions in which a player gets a steal, and is adjusted for time spent on the court:

STL\_PCT=\frac{STL}{\frac{MIN}{32}\times OPP\_POSS}

Block Percentage (BLK_PCT) - proportion of opponent’s possessions in which a player gets a block, and is adjusted for time spent on the court:

BLK\_PCT=\frac{BLK}{\frac{MIN}{32}\times OPP\_POSS}

Turnover Percentage (TOV_PCT) - measures the proportion of player turnovers per team possession, and is adjusted for time spent on the court:

TOV\_PCT=\frac{TO}{\frac{MIN}{32}\times TLT\_POSS}

Player Efficiency (EFF) - this is a (somewhat crude) measure of player efficiency, which is an adjusted difference of positive stats to negative stats:

1. positive stats: points, rebounds, assists, steals and blocks
2. negative stats: missed field goals, missed free throws and turnovers

The sum of positive stats minus the sum of negative stats is then adjusted for time spent on the court:

$$ = (++++ \ -(-)-(-)-)

$$